CREATE TABLE IF NOT EXISTS organization_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    logo_path VARCHAR(255) DEFAULT NULL,
    date_format VARCHAR(30) DEFAULT 'dd/MM/yyyy',
    field_separator VARCHAR(5) DEFAULT '/',
    primary_contact_name VARCHAR(150) DEFAULT NULL,
    primary_contact_email VARCHAR(150) DEFAULT NULL,
    sender_email VARCHAR(150) DEFAULT 'message-service@mail.zohopayroll.in',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_settings (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS branding_preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    appearance ENUM('dark', 'light') NOT NULL DEFAULT 'dark',
    accent_color VARCHAR(30) NOT NULL DEFAULT 'blue',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_branding_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS work_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    location_name VARCHAR(150) NOT NULL,
    address_line1 VARCHAR(200) DEFAULT NULL,
    address_line2 VARCHAR(200) DEFAULT NULL,
    state VARCHAR(100) DEFAULT NULL,
    city VARCHAR(100) DEFAULT NULL,
    pincode VARCHAR(20) DEFAULT NULL,
    is_filing_address TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_location_name (organization_id, location_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    org_id VARCHAR(50) NOT NULL,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(80) DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_department_name (org_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS designations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    name VARCHAR(120) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_designation (organization_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    plan_name VARCHAR(120) NOT NULL DEFAULT '14-DAYS FREE TRIAL',
    billing_cycle VARCHAR(30) NOT NULL DEFAULT 'monthly',
    monthly_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    employee_limit INT NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'trial',
    trial_ends_on DATE DEFAULT NULL,
    feature_summary TEXT DEFAULT NULL,
    premium_feature_summary TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_subscription (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    role_name VARCHAR(150) NOT NULL,
    description VARCHAR(500) DEFAULT NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    permissions_json LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_role (organization_id, role_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    full_name VARCHAR(180) NOT NULL,
    email VARCHAR(190) NOT NULL,
    role_name VARCHAR(150) NOT NULL DEFAULT 'Admin',
    status VARCHAR(30) NOT NULL DEFAULT 'ACTIVE',
    avatar_path VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_user_email (organization_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pay_schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    pay_frequency VARCHAR(100) NOT NULL DEFAULT 'Every month',
    working_days_json TEXT DEFAULT NULL,
    pay_day_label VARCHAR(120) DEFAULT NULL,
    first_pay_period VARCHAR(60) DEFAULT NULL,
    first_pay_date DATE DEFAULT NULL,
    working_days_count INT NOT NULL DEFAULT 0,
    upcoming_payrolls_json LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_pay_schedule (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS statutory_components (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT NOT NULL,
    component_code VARCHAR(40) NOT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
    config_json LONGTEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_org_component (organization_id, component_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
