-- Database Schema for Website & Google Play Console Management System


START TRANSACTION;


-- --------------------------------------------------------

--
-- Table structure for table `website_records`
--

CREATE TABLE `website_records` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `website_name` varchar(255) NOT NULL,
    `customer_name` varchar(100) NOT NULL,
    `customer_mobile` varchar(20) NOT NULL,
    `hosting_start_date` date DEFAULT NULL,
    `hosting_end_date` date DEFAULT NULL,
    `domain_start_date` date DEFAULT NULL,
    `domain_end_date` date DEFAULT NULL,
    `hosting_buy_place` varchar(100) DEFAULT NULL,
    `domain_buy_place` varchar(100) DEFAULT NULL,
    `customer_payment` decimal(10, 2) DEFAULT 0.00,
    `renewal_payment` decimal(10, 2) DEFAULT 0.00,
    `hosting_change` enum('Yes', 'No') DEFAULT 'No',
    `notes` text DEFAULT NULL,
    `created_date` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `documents`
--

CREATE TABLE `documents` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `play_console_id` int(11) NOT NULL,
    `doc_type` enum(
        'Aadhaar',
        'PAN',
        'BankStatement',
        'AddressProof',
        'Other'
    ) NOT NULL,
    `file_path` varchar(255) NOT NULL,
    `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    FOREIGN KEY (`play_console_id`) REFERENCES `play_console_accounts` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `notes`
--

CREATE TABLE `notes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `related_type` enum('Website', 'PlayConsole') NOT NULL,
    `related_id` int(11) NOT NULL,
    `admin_name` varchar(50) NOT NULL,
    `description` text NOT NULL,
    `created_date` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `related_type` enum('Website', 'PlayConsole') NOT NULL,
    `related_id` int(11) NOT NULL,
    `amount` decimal(10, 2) NOT NULL,
    `payment_date` date NOT NULL,
    `status` enum('Paid', 'Pending') NOT NULL DEFAULT 'Pending',
    `payment_mode` varchar(50) DEFAULT NULL,
    `notes` text DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

COMMIT;