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

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

START TRANSACTION;

SET time_zone = "+00:00";

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

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(50) NOT NULL,
    `password` varchar(255) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

--
-- Dumping data for table `admins`
--
-- Default login: admin / admin123
INSERT INTO
    `admins` (`username`, `password`)
VALUES (
        'admin',
        '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'
    );

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

--
-- 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 `play_console_accounts`
--

CREATE TABLE `play_console_accounts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `record_date` date NOT NULL,
    `console_name` varchar(100) NOT NULL, -- Name (from prompt)
    `mobile_number` varchar(20) NOT NULL,
    `recovery_number` varchar(20) DEFAULT NULL,
    `new_mobile_number` varchar(20) DEFAULT NULL,
    `account_year` varchar(10) DEFAULT NULL,
    `app_link` varchar(255) DEFAULT NULL,
    `account_holder_name` varchar(100) DEFAULT NULL, -- Account Name
    `payment_cost` decimal(10, 2) DEFAULT 0.00, -- Payment of Account
    `sell_price` decimal(10, 2) DEFAULT 0.00,
    `login_id` varchar(100) NOT NULL,
    `login_password` text NOT NULL, -- Encrypted
    `new_password` text DEFAULT NULL, -- Encrypted
    `agent_name` varchar(100) DEFAULT NULL,
    `payment_mode` varchar(50) DEFAULT NULL,
    `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;