CREATE TABLE IF NOT EXISTS subscription_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    billing_cycle ENUM('monthly','yearly') DEFAULT 'monthly',
    max_consumers INT DEFAULT NULL,
    max_staff INT DEFAULT NULL,
    max_locations INT DEFAULT NULL,
    features JSON,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    plan_id INT NOT NULL,
    status ENUM('active','trialing','cancelled','past_due','expired') DEFAULT 'active',
    current_period_start DATE,
    current_period_end DATE,
    cancelled_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organization_id) REFERENCES organizations(id),
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id)
);

CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    invoice_number VARCHAR(50),
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    status ENUM('draft','pending','paid','overdue','void') DEFAULT 'pending',
    description VARCHAR(255),
    paid_at TIMESTAMP NULL,
    due_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organization_id) REFERENCES organizations(id)
);

CREATE TABLE IF NOT EXISTS invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    description VARCHAR(255),
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10,2) DEFAULT 0,
    amount DECIMAL(10,2) DEFAULT 0,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);

-- Seed default plans
INSERT INTO subscription_plans (name, slug, price, billing_cycle, max_consumers, max_staff, max_locations, features) VALUES
('Starter', 'starter', 99.00, 'monthly', 10, 5, 1, '["Basic reporting","Email support","Core modules"]'),
('Professional', 'professional', 249.00, 'monthly', 50, 25, 5, '["Advanced reporting","Priority support","All modules","AI Assistant","Custom compliance templates"]'),
('Enterprise', 'enterprise', 499.00, 'monthly', NULL, NULL, NULL, '["Unlimited everything","Dedicated support","Custom integrations","API access","White labeling","Multi-state compliance"]');