/*
==========================================================
 Repurev Database Schema (Normalized & Role-Based Access)
==========================================================
 Author  : Solution Architect
 Purpose : Provides normalized structure for Companies,
           Users, Roles, and Permissions with RBAC.
 Notes   :
 - Follows PostgreSQL best practices (snake_case, SERIAL PKs).
 - Splits repeating/nullable fields into supporting tables.
 - Supports role-based access control via repo_role + repo_module.
==========================================================
*/

----------------------------------------------------------
-- 1. Company Core Table
----------------------------------------------------------
CREATE TABLE repo_company (
    company_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    domain TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------
-- 1.a Company Social Links (normalized)
----------------------------------------------------------
CREATE TABLE repo_company_social (
    social_id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES repo_company(company_id) ON DELETE CASCADE,
    platform TEXT NOT NULL,   -- e.g., 'linkedin', 'instagram', 'x', 'youtube'
    handle TEXT,
    url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------
-- 1.b Company Product Links (Clutch, G2, Capterra)
----------------------------------------------------------
CREATE TABLE repo_company_product_link (
    product_link_id SERIAL PRIMARY KEY,
    company_id INT NOT NULL REFERENCES repo_company(company_id) ON DELETE CASCADE,
    platform TEXT NOT NULL,   -- e.g., 'clutch', 'g2', 'capterra'
    url TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------
-- 2. User Core Table
----------------------------------------------------------
CREATE TABLE repo_user (
    user_id SERIAL PRIMARY KEY,
    company_id INT REFERENCES repo_company(company_id) ON DELETE SET NULL,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------
-- 2.a User Flags (flexible boolean attributes)
----------------------------------------------------------
CREATE TABLE repo_user_flags (
    flag_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES repo_user(user_id) ON DELETE CASCADE,
    flag_key TEXT NOT NULL,       -- e.g., 'is_monetized', 'lifetime_enabled'
    flag_value BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------
-- 2.b User Security (tokens, reset info)
----------------------------------------------------------
CREATE TABLE repo_user_security (
    user_id INT PRIMARY KEY REFERENCES repo_user(user_id) ON DELETE CASCADE,
    verification_token TEXT,
    password_reset_token TEXT,
    password_reset_expires_at TIMESTAMPTZ,
    jwt_token TEXT,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

----------------------------------------------------------
-- 3. Roles (RBAC)
----------------------------------------------------------
CREATE TABLE repo_role (
    role_id SERIAL PRIMARY KEY,
    role_name TEXT NOT NULL UNIQUE,     -- e.g., 'customer', 'cmo_huddler', 'super_admin'
    description TEXT
);

----------------------------------------------------------
-- 4. Modules (System Features)
----------------------------------------------------------
CREATE TABLE repo_module (
    module_id SERIAL PRIMARY KEY,
    module_name TEXT NOT NULL UNIQUE,   -- e.g., 'user_listing', 'company_listing'
    description TEXT
);

----------------------------------------------------------
-- 5. User ↔ Role Mapping (many-to-many)
----------------------------------------------------------
CREATE TABLE repo_user_role (
    user_id INT NOT NULL REFERENCES repo_user(user_id) ON DELETE CASCADE,
    role_id INT NOT NULL REFERENCES repo_role(role_id) ON DELETE CASCADE,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

----------------------------------------------------------
-- 6. Role ↔ Module Mapping (RBAC permissions)
----------------------------------------------------------
CREATE TABLE repo_role_module (
    role_id INT NOT NULL REFERENCES repo_role(role_id) ON DELETE CASCADE,
    module_id INT NOT NULL REFERENCES repo_module(module_id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, module_id)
);

==========================================================
-- SAMPLE DATA INSERTS
==========================================================

-- Insert Companies
INSERT INTO repo_company (name, domain) VALUES
('2X Marketing', '2x.marketing'),
('Growth Natives', 'growthnatives.com');

-- Insert Company Social Links
INSERT INTO repo_company_social (company_id, platform, handle, url) VALUES
(1, 'linkedin', '2xmarketing', 'https://linkedin.com/company/2xmarketing'),
(2, 'linkedin', 'growthnatives', 'https://linkedin.com/company/growthnatives');

-- Insert Company Product Links
INSERT INTO repo_company_product_link (company_id, platform, url) VALUES
(1, 'clutch', 'https://clutch.co/profile/2x'),
(2, 'g2', 'https://www.g2.com/products/growthnatives/reviews');

-- Insert Users
INSERT INTO repo_user (company_id, email, password_hash, first_name, last_name) VALUES
(1, 'alice@2x.marketing', 'hashedpass1', 'Alice', 'Smith'),
(2, 'bob@growthnatives.com', 'hashedpass2', 'Bob', 'Johnson');

-- Insert Roles
INSERT INTO repo_role (role_name, description) VALUES
('customer', 'Standard customer user'),
('cmo_huddler', 'Premium paid customer'),
('super_admin', 'Full platform administrator');

-- Insert Modules
INSERT INTO repo_module (module_name, description) VALUES
('user_listing', 'View/manage all users'),
('company_listing', 'View/manage all companies'),
('analytic_reports', 'View global analytic reports'),
('own_profile', 'Manage own profile'),
('own_company', 'Manage own company details'),
('own_company_analytic_reports', 'View company analytic reports');

-- Assign Roles to Users
INSERT INTO repo_user_role (user_id, role_id) VALUES
(1, 1),  -- Alice as Customer
(2, 2);  -- Bob as CMO Huddler

-- Assign Permissions to Roles
-- Super Admin gets all admin modules
INSERT INTO repo_role_module (role_id, module_id) VALUES
(3, 1), (3, 2), (3, 3);

-- Customer gets only self modules
INSERT INTO repo_role_module (role_id, module_id) VALUES
(1, 4), (1, 5), (1, 6);

-- CMO Huddler gets same self modules
INSERT INTO repo_role_module (role_id, module_id) VALUES
(2, 4), (2, 5), (2, 6);