-- FAMILY360 Phase 1 schema (MySQL 8 / MariaDB 10.5+, InnoDB, utf8mb4)
-- Import in phpMyAdmin: select your database > Import > this file.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS households (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(120) NOT NULL,
    currency    CHAR(3)      NOT NULL DEFAULT 'KES',
    timezone    VARCHAR(64)  NOT NULL DEFAULT 'Africa/Nairobi',
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id   INT UNSIGNED NOT NULL,
    name           VARCHAR(100) NOT NULL,
    email          VARCHAR(190) NOT NULL,
    password_hash  VARCHAR(255) NOT NULL,
    role           ENUM('admin','member') NOT NULL DEFAULT 'member',
    is_active      TINYINT(1)   NOT NULL DEFAULT 1,
    last_login_at  DATETIME     NULL,
    created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_household (household_id),
    CONSTRAINT fk_users_household FOREIGN KEY (household_id) REFERENCES households (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id  INT UNSIGNED NOT NULL,
    type          ENUM('income','expense') NOT NULL,
    name          VARCHAR(80)  NOT NULL,
    is_active     TINYINT(1)   NOT NULL DEFAULT 1,
    UNIQUE KEY uq_category (household_id, type, name),
    CONSTRAINT fk_categories_household FOREIGN KEY (household_id) REFERENCES households (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Visibility: 'shared' is visible to every household member.
-- 'private' is visible only to private_owner_id (e.g. Jack private, Mary private).
CREATE TABLE IF NOT EXISTS transactions (
    id                   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id         INT UNSIGNED  NOT NULL,
    type                 ENUM('income','expense') NOT NULL,
    category_id          INT UNSIGNED  NOT NULL,
    amount               DECIMAL(14,2) NOT NULL,
    occurred_on          DATE          NOT NULL,
    recorded_by          INT UNSIGNED  NOT NULL,
    responsible_user_id  INT UNSIGNED  NULL,
    payment_method       VARCHAR(40)   NULL,
    description          VARCHAR(255)  NULL,
    visibility           ENUM('shared','private') NOT NULL DEFAULT 'shared',
    private_owner_id     INT UNSIGNED  NULL,
    deleted_at           DATETIME      NULL,
    created_at           TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_tx_household_date (household_id, occurred_on),
    KEY idx_tx_category (category_id),
    CONSTRAINT fk_tx_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_tx_category  FOREIGN KEY (category_id) REFERENCES categories (id),
    CONSTRAINT fk_tx_recorded  FOREIGN KEY (recorded_by) REFERENCES users (id),
    CONSTRAINT fk_tx_owner     FOREIGN KEY (private_owner_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS budgets (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id  INT UNSIGNED  NOT NULL,
    category_id   INT UNSIGNED  NOT NULL,
    period        ENUM('weekly','monthly') NOT NULL DEFAULT 'monthly',
    amount        DECIMAL(14,2) NOT NULL,
    starts_on     DATE          NOT NULL,
    ends_on       DATE          NULL,
    created_by    INT UNSIGNED  NOT NULL,
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_budget_household (household_id),
    CONSTRAINT fk_budget_household FOREIGN KEY (household_id) REFERENCES households (id),
    CONSTRAINT fk_budget_category  FOREIGN KEY (category_id) REFERENCES categories (id),
    CONSTRAINT fk_budget_user      FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    household_id  INT UNSIGNED NOT NULL,
    user_id       INT UNSIGNED NULL,
    action        VARCHAR(40)  NOT NULL,
    entity        VARCHAR(40)  NOT NULL,
    entity_id     VARCHAR(40)  NOT NULL,
    ip            VARCHAR(45)  NULL,
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_household (household_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ip            VARCHAR(45) NOT NULL,
    attempted_at  DATETIME    NOT NULL,
    KEY idx_login_ip (ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Starting data
INSERT IGNORE INTO households (id, name, currency, timezone) VALUES (1, 'Our Household', 'KES', 'Africa/Nairobi');

INSERT IGNORE INTO categories (household_id, type, name) VALUES
(1,'income','Salary'),(1,'income','Business income'),(1,'income','Consultancy'),
(1,'income','Projects'),(1,'income','Gifts'),(1,'income','Other income'),
(1,'expense','Food and groceries'),(1,'expense','Rent'),(1,'expense','Electricity'),
(1,'expense','Water'),(1,'expense','Internet'),(1,'expense','Transport'),
(1,'expense','School expenses'),(1,'expense','Healthcare'),(1,'expense','Clothing'),
(1,'expense','Entertainment'),(1,'expense','Household purchases'),
(1,'expense','Personal expenses'),(1,'expense','Debt payments'),(1,'expense','Other');