-- ============================================================
-- Profit & Expense Automation System - Database Schema
-- ============================================================

CREATE DATABASE IF NOT EXISTS `profit_tracker` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `profit_tracker`;

-- Users
CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin','viewer') DEFAULT 'admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Email configuration per user
CREATE TABLE `email_configs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `imap_host` VARCHAR(150) NOT NULL,
  `imap_port` SMALLINT DEFAULT 993,
  `imap_user` VARCHAR(150) NOT NULL,
  `imap_password` VARCHAR(255) NOT NULL,
  `use_ssl` TINYINT(1) DEFAULT 1,
  `last_synced_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Raw emails
CREATE TABLE `emails` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `uid` VARCHAR(100) NOT NULL,
  `sender` VARCHAR(200),
  `subject` VARCHAR(500),
  `received_at` TIMESTAMP NULL,
  `raw_body` LONGTEXT,
  `status` ENUM('pending','processed','skipped','error') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- PDF attachments
CREATE TABLE `attachments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email_id` INT UNSIGNED NOT NULL,
  `filename` VARCHAR(300),
  `filepath` VARCHAR(500),
  `parsed` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`email_id`) REFERENCES `emails`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Parsed invoices
CREATE TABLE `invoices` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email_id` INT UNSIGNED,
  `attachment_id` INT UNSIGNED,
  `invoice_number` VARCHAR(100),
  `invoice_date` DATE,
  `due_date` DATE NULL,
  `service_type` ENUM('hosting','vps_sale','vps_license','domain','other') NOT NULL,
  `product_name` VARCHAR(300),
  `domain_name` VARCHAR(300) NULL,
  `billing_period_start` DATE NULL,
  `billing_period_end` DATE NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `tax` DECIMAL(12,2) DEFAULT 0,
  `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
  `currency` VARCHAR(10) DEFAULT 'USD',
  `classification` ENUM('profit','expense') NOT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`email_id`) REFERENCES `emails`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`attachment_id`) REFERENCES `attachments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Domains tracking
CREATE TABLE `domains` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT UNSIGNED,
  `domain_name` VARCHAR(300) NOT NULL,
  `extension` VARCHAR(20),
  `type` ENUM('new','renewal','transfer') DEFAULT 'new',
  `cost` DECIMAL(12,2) NOT NULL,
  `registered_date` DATE NULL,
  `expiry_date` DATE NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Hosting revenue
CREATE TABLE `hosting_revenue` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT UNSIGNED,
  `package_name` VARCHAR(200),
  `client_name` VARCHAR(200),
  `revenue` DECIMAL(12,2) NOT NULL,
  `period` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- VPS records
CREATE TABLE `vps_records` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `invoice_id` INT UNSIGNED,
  `type` ENUM('sale','license') NOT NULL,
  `product_name` VARCHAR(200),
  `client_name` VARCHAR(200) NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `period` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`invoice_id`) REFERENCES `invoices`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Monthly reports (cached)
CREATE TABLE `monthly_reports` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `year` SMALLINT NOT NULL,
  `month` TINYINT NOT NULL,
  `hosting_revenue` DECIMAL(14,2) DEFAULT 0,
  `vps_revenue` DECIMAL(14,2) DEFAULT 0,
  `total_profit` DECIMAL(14,2) DEFAULT 0,
  `domain_expense` DECIMAL(14,2) DEFAULT 0,
  `vps_license_expense` DECIMAL(14,2) DEFAULT 0,
  `total_expense` DECIMAL(14,2) DEFAULT 0,
  `net_profit` DECIMAL(14,2) DEFAULT 0,
  `generated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `year_month` (`year`,`month`)
) ENGINE=InnoDB;

-- Parser rules (regex/keyword based)
CREATE TABLE `parser_rules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `match_field` ENUM('subject','sender','body','filename') NOT NULL,
  `match_pattern` VARCHAR(500) NOT NULL,
  `service_type` ENUM('hosting','vps_sale','vps_license','domain','other') NOT NULL,
  `classification` ENUM('profit','expense') NOT NULL,
  `priority` INT DEFAULT 10,
  `active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Activity log
CREATE TABLE `activity_log` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `type` VARCHAR(50),
  `message` TEXT,
  `data` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- Default data
-- ============================================================

-- Default admin: admin@example.com / admin123 (change immediately!)
INSERT INTO `users` (`name`,`email`,`password`,`role`) VALUES
('Admin','admin@example.com','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi.','admin');

-- Default parser rules
INSERT INTO `parser_rules` (`name`,`match_field`,`match_pattern`,`service_type`,`classification`,`priority`) VALUES
('Domain registration - subject','subject','domain|registration|whois','domain','expense',10),
('Domain renewal - subject','subject','renewal|renew','domain','expense',10),
('Hosting invoice - subject','subject','hosting|web hosting|cpanel|shared hosting','hosting','profit',20),
('VPS invoice - subject','subject','vps|virtual private|cloud server','vps_sale','profit',20),
('VPS license - body','body','cPanel license|DirectAdmin|Plesk|license fee','vps_license','expense',15),
('WHMCS order - subject','subject','order confirmation|new order|invoice','hosting','profit',5);