CREATE DATABASE IF NOT EXISTS gst_calculator CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE gst_calculator; CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(180) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin', 'manager', 'merchant') NOT NULL DEFAULT 'merchant', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); 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 users (name, email, password_hash, role) VALUES ('Administrator', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC5eWutEGZ2sdMsHnGAQJwemnBcdW', 'admin') ON DUPLICATE KEY UPDATE name = VALUES(name), password_hash = VALUES(password_hash), role = VALUES(role); 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;