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

CREATE TABLE IF NOT EXISTS plans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL,
    slug VARCHAR(80) NOT NULL UNIQUE,
    price_monthly DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    invoice_limit INT UNSIGNED NOT NULL DEFAULT 100,
    user_limit INT UNSIGNED NOT NULL DEFAULT 1,
    features TEXT NULL,
    is_popular TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_profiles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL UNIQUE,
    company_name VARCHAR(180) NULL,
    phone VARCHAR(30) NULL,
    gst_number VARCHAR(30) NULL,
    address TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS subscriptions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    plan_id INT UNSIGNED NOT NULL,
    status ENUM('trial', 'active', 'pending', 'expired', 'cancelled') NOT NULL DEFAULT 'active',
    starts_at DATE NOT NULL,
    ends_at DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscriptions_user (user_id),
    INDEX idx_subscriptions_status (status),
    CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES plans(id)
);

CREATE TABLE IF NOT EXISTS company_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(180) NOT NULL,
    gst_number VARCHAR(30) NOT NULL,
    address TEXT NOT NULL,
    logo_url VARCHAR(255) NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(180) NULL,
    invoice_prefix VARCHAR(20) NOT NULL DEFAULT 'GST',
    next_invoice_number INT UNSIGNED NOT NULL DEFAULT 1,
    default_hsn_sac VARCHAR(30) NOT NULL DEFAULT '998413',
    default_gst_rate DECIMAL(8,4) NOT NULL DEFAULT 18.0000,
    default_tds_rate DECIMAL(8,4) NOT NULL DEFAULT 2.0000,
    default_commission_rate DECIMAL(8,4) NOT NULL DEFAULT 0.2000,
    bank_details TEXT NULL,
    signature_name VARCHAR(120) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(60) NOT NULL UNIQUE,
    invoice_date DATE NOT NULL,
    calculator_type ENUM('p2p', 'p2a') NOT NULL,
    gst_mode ENUM('inclusive', 'exclusive') NOT NULL,
    customer_name VARCHAR(180) NOT NULL,
    customer_gst VARCHAR(30) NULL,
    customer_address TEXT NULL,
    customer_email VARCHAR(180) NULL,
    customer_phone VARCHAR(30) NULL,
    hsn_sac VARCHAR(30) NOT NULL,
    place_of_supply VARCHAR(80) NULL,
    load_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    transaction_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    average_commission_percent DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    commission_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    taxable_amount DECIMAL(14,2) NOT NULL,
    gst_rate DECIMAL(8,4) NOT NULL DEFAULT 18.0000,
    gst_amount DECIMAL(14,2) NOT NULL,
    cgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    sgst_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    igst_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    invoice_amount DECIMAL(14,2) NOT NULL,
    tds_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    tds_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    reimbursement_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    round_off DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(14,2) NOT NULL,
    status ENUM('draft', 'issued', 'paid', 'cancelled') NOT NULL DEFAULT 'issued',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_invoice_date (invoice_date),
    INDEX idx_calculator_type (calculator_type),
    INDEX idx_customer_name (customer_name)
);

INSERT INTO plans (name, slug, price_monthly, invoice_limit, user_limit, features, is_popular, is_active)
VALUES
    ('Starter', 'starter', 499.00, 100, 1, 'P2P GST Calculator|P2A Reimbursement Calculator|Invoice PDF Download|Basic History', 0, 1),
    ('Professional', 'professional', 999.00, 1000, 3, 'Everything in Starter|Excel Export|Monthly GST Summary|Multiple GST Profiles|Priority Support', 1, 1),
    ('Business', 'business', 2499.00, 10000, 10, 'Everything in Professional|Multiple Business Accounts|Admin Panel|Advanced Analytics|Custom Branding', 0, 1)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price_monthly = VALUES(price_monthly),
    invoice_limit = VALUES(invoice_limit),
    user_limit = VALUES(user_limit),
    features = VALUES(features),
    is_popular = VALUES(is_popular),
    is_active = VALUES(is_active);

INSERT INTO company_settings (
    id,
    company_name,
    gst_number,
    address,
    logo_url,
    phone,
    email,
    invoice_prefix,
    next_invoice_number,
    default_hsn_sac,
    default_gst_rate,
    default_tds_rate,
    default_commission_rate,
    signature_name
) VALUES (
    1,
    'Your Fintech Pvt Ltd',
    '27ABCDE1234F1Z5',
    'Business Address, City, State, India',
    '',
    '+91 99999 99999',
    'billing@example.com',
    'GST',
    1,
    '998413',
    18.0000,
    2.0000,
    0.2000,
    'Authorized Signatory'
)
ON DUPLICATE KEY UPDATE id = id;
