-- =============================================
-- 头寸管理系统 - 建表脚本 (Oracle 19c)
-- =============================================

-- 头寸表
CREATE TABLE T_POSITION (
    ID              NUMBER(19)    NOT NULL,
    POSITION_CODE   VARCHAR2(64)  NOT NULL,
    POSITION_NAME   VARCHAR2(128) NOT NULL,
    POSITION_TYPE   VARCHAR2(32)  NOT NULL,
    DIRECTION       VARCHAR2(16)  NOT NULL,
    QUANTITY        NUMBER(18,4)  DEFAULT 0,
    PRICE           NUMBER(18,4)  DEFAULT 0,
    MARKET_VALUE    NUMBER(18,4)  DEFAULT 0,
    ACCOUNT_CODE    VARCHAR2(64),
    PRODUCT_CODE    VARCHAR2(64),
    STATUS          VARCHAR2(16)  DEFAULT 'ACTIVE',
    VERSION         NUMBER(10)    DEFAULT 0,
    DELETED         NUMBER(1)     DEFAULT 0,
    CREATE_TIME     TIMESTAMP     DEFAULT SYSDATE,
    UPDATE_TIME     TIMESTAMP     DEFAULT SYSDATE,
    CONSTRAINT PK_T_POSITION PRIMARY KEY (ID),
    CONSTRAINT UK_T_POSITION_CODE UNIQUE (POSITION_CODE)
);

COMMENT ON TABLE  T_POSITION              IS '头寸表';
COMMENT ON COLUMN T_POSITION.ID              IS '主键ID';
COMMENT ON COLUMN T_POSITION.POSITION_CODE   IS '头寸编号（业务唯一编号）';
COMMENT ON COLUMN T_POSITION.POSITION_NAME   IS '头寸名称';
COMMENT ON COLUMN T_POSITION.POSITION_TYPE   IS '头寸类型：TRADING-交易头寸，INVESTMENT-投资头寸，SETTLEMENT-结算头寸';
COMMENT ON COLUMN T_POSITION.DIRECTION      IS '方向：LONG-多头，SHORT-空头';
COMMENT ON COLUMN T_POSITION.QUANTITY       IS '数量';
COMMENT ON COLUMN T_POSITION.PRICE           IS '价格';
COMMENT ON COLUMN T_POSITION.MARKET_VALUE    IS '市值';
COMMENT ON COLUMN T_POSITION.ACCOUNT_CODE    IS '账户编号';
COMMENT ON COLUMN T_POSITION.PRODUCT_CODE    IS '产品编号';
COMMENT ON COLUMN T_POSITION.STATUS          IS '状态：ACTIVE-有效，CLOSED-已平仓';
COMMENT ON COLUMN T_POSITION.VERSION         IS '乐观锁版本号';
COMMENT ON COLUMN T_POSITION.DELETED         IS '逻辑删除：0-未删除，1-已删除';
COMMENT ON COLUMN T_POSITION.CREATE_TIME     IS '创建时间';
COMMENT ON COLUMN T_POSITION.UPDATE_TIME     IS '更新时间';

-- 创建序列（Oracle 自增ID）
CREATE SEQUENCE SEQ_T_POSITION START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

-- 创建索引
CREATE INDEX IDX_T_POSITION_TYPE     ON T_POSITION(POSITION_TYPE);
CREATE INDEX IDX_T_POSITION_STATUS   ON T_POSITION(STATUS);
CREATE INDEX IDX_T_POSITION_ACCOUNT  ON T_POSITION(ACCOUNT_CODE);
CREATE INDEX IDX_T_POSITION_PRODUCT  ON T_POSITION(PRODUCT_CODE);